import java.sql.Connection;
import java.sql.DriverManager;

import org.codehaus.groovy.grails.io.support.ClassPathResource

import com.macrosolution.exdebito.AnomaliaBancariaType
import com.macrosolution.exdebito.Comune;
import com.macrosolution.exdebito.DebitType
import com.macrosolution.exdebito.EventUser
import com.macrosolution.exdebito.FamilyType
import com.macrosolution.exdebito.ImmobileType
import com.macrosolution.exdebito.JobType
import com.macrosolution.exdebito.Message
import com.macrosolution.exdebito.MotiveInterviewType
import com.macrosolution.exdebito.Provincia;
import com.macrosolution.exdebito.Regione;

import com.macrosolution.exdebito.Role;
import com.macrosolution.exdebito.User;
import com.macrosolution.exdebito.UserRole;
import com.macrosolution.exdebito.DocType;
import groovy.sql.Sql


class BootStrap {
	def grailsApplication
	def dataSource
	def init = { servletContext ->
		
		def adminRole = Role.findByAuthority('ROLE_ADMIN') ?: 
						new Role(authority: 'ROLE_ADMIN',priority:1,label:'ADMIN').save(failOnError: true)
		def ammRole = Role.findByAuthority('ROLE_AMM') ?: 
						new Role(authority: 'ROLE_AMM',priority:2,label:'AMMINISTRATORE').save(failOnError: true)		
		def boRole = Role.findByAuthority('ROLE_BCKOFF') ?:
					new Role(authority: 'ROLE_BCKOFF',priority:3,label:'BACK-OFFICE').save(failOnError: true)
		def avvRole = Role.findByAuthority('ROLE_AVV') ?:
					new Role(authority: 'ROLE_AVV',priority:3,label:'AVVOCATO',isReadOnly:true,hasParentPrivilege:true).save(failOnError: true)
		def ccRole = Role.findByAuthority('ROLE_CC') ?:
					new Role(authority: 'ROLE_CC',priority:4,label:'CAPO STRUTTURA').save(failOnError: true)	
		def cRole = Role.findByAuthority('ROLE_C') ?:
					new Role(authority: 'ROLE_C',priority:5,label:'COLLABORATORE').save(failOnError: true)
		def scRole = Role.findByAuthority('ROLE_SC') ?:
					new Role(authority: 'ROLE_SC',priority:6,label:'SEGNALATORE').save(failOnError: true)
		def customerRole = Role.findByAuthority('ROLE_CUSTOMER') ?:
					new Role(authority: 'ROLE_CUSTOMER',priority:7,label:'CLIENTE').save(failOnError: true)
			
		def adminUser = User.findByUsername('admin_mr_rights') ?: new User(
			username: 'admin_mr_rights',
			password: 'adminmrrights2015',
			email:'admin@mr_rights.it',
			name:'Marco',
			surname:'Cerquozzi',
			lg:'it',
		enabled: true).save(failOnError: true)		
//		def collaboratorUser = User.findByUsername('collaborator_exdebito') ?: new User(
//			username: 'collaborator_exdebito',
//			password: 'collaboratorexdebito2015',
//			email:'collaborator@exedebito.it',
//			lg:'it',
//		enabled: true).save(failOnError: true)
//		def collaboratorUser2 = User.findByUsername('collaborator2_exdebito') ?: new User(
//			username: 'collaborator2_exdebito',
//			password: 'collaboratorexdebito2015',
//			email:'collaborator@exedebito.it',
//			lg:'it',
//		enabled: true).save(failOnError: true)
//		
//		def ccUser = User.findByUsername('cc_exdebito') ?: new User(
//			username: 'cc_exdebito',
//			password: 'ccexdebito2015',
//			email:'collaborator@exedebito.it',
//			lg:'it',
//		enabled: true).save(failOnError: true)
				
		UserRole usrAdm = UserRole.findByUser(adminUser)
		if(!usrAdm){
			UserRole uRadmin = new UserRole()
			uRadmin.create(adminUser, adminRole).save(flush:true)
//			ccUser.addSubject(collaboratorUser);
//			adminUser.addSubject(ccUser);
		}
		
//		UserRole usrCol = UserRole.findByUser(collaboratorUser)
//		if(!usrCol){
//			UserRole urColl = new UserRole()
//			urColl.create(collaboratorUser, cRole).save(flush:true)
//			urColl.create(collaboratorUser2,cRole).save(flush:true)
//			adminUser.addSubject(collaboratorUser2);
//		}
//		UserRole usrCc = UserRole.findByUser(ccUser)
//		if(!usrCc){
//			UserRole urColl = new UserRole()
//			urColl.create(ccUser, ccRole).save(flush:true)
//		}
		
		
		//Document Type		
		def docTypeModulistica = DocType.findByDocumentType('Modulistica Perizie') ?:
		new DocType(
			documentType : 'Modulistica Perizie',
			documentCategory:1
			
			).save(failOnError: true)
			def docTypeModulisticaPer = DocType.findByDocumentType('Modulistica Sovraindebitamento') ?:
			new DocType(
				documentType : 'Modulistica Sovraindebitamento',
				documentCategory:1
				
				).save(failOnError: true)
				def docTypeModulisticaSovr = DocType.findByDocumentType('Modulistica Consulenza Aziendale') ?:
				new DocType(
					documentType : 'Modulistica Consulenza Aziendale',
					documentCategory:1
					
					).save(failOnError: true)
		
		def docTypeApprofondimenti = DocType.findByDocumentType('Approfondimenti') ?:
		new DocType(
			documentType : 'Approfondimenti',
			documentCategory:1
			).save(failOnError: true)
		
		def docTypeStrumenti = DocType.findByDocumentType('Strumenti') ?:
		new DocType(
			documentType : 'Strumenti',
			documentCategory:1
			).save(failOnError: true)
		
		
			
			//crea directory per il conf
			def folder = new File( File.separator+'upload' )
			if( !folder.exists() ) {				
				folder.mkdirs()
			 }
			
			
			//Cartelle e doctype anomaliaBancaria
			
			def docTypeAnagrafica = DocType.findByDocumentType('Anagrafica') ?:
			new DocType(
					documentType : 'Anagrafica',
					documentCategory:2
			).save(failOnError: true)
			
			def docTypeModuli = DocType.findByDocumentType('Moduli') ?:
			new DocType(
				documentType : 'Moduli',
				documentCategory:2
			).save(failOnError: true)
			
			def docTypeDocumenti = DocType.findByDocumentType('Documenti') ?:
			new DocType(
				documentType : 'Documenti',
				documentCategory:2
				).save(failOnError: true)
			
			def docTypePreanalisi = DocType.findByDocumentType('Preanalisi') ?:
			new DocType(
				documentType : 'Preanalisi',
				documentCategory:2
			).save(failOnError: true)
			
			def docTypePerizia = DocType.findByDocumentType('Perizia') ?:
			new DocType(
				documentType : 'Perizia',
				documentCategory:2
				).save(failOnError: true)
				
			//Fileinterview
				
//			def docTypeItervista = DocType.findByDocumentType('Intervista') ?:
//			new DocType(
//				documentType : 'Intervista',
//				documentCategory:3
//			).save(failOnError: true)
			
			def docTypeIntCodf = DocType.findByDocumentTypeAndDocumentCategory('Codice Fiscale',3) ?:
			new DocType(
				documentType : 'Codice Fiscale',
				documentCategory:3
			).save(failOnError: true)
			
			def docTypeIntDocRic = DocType.findByDocumentTypeAndDocumentCategory('Documento di riconoscimento',3) ?:
			new DocType(
				documentType : 'Documento di riconoscimento',
				documentCategory:3
			).save(failOnError: true)
			
			def docTypeIntPrivacy = DocType.findByDocumentTypeAndDocumentCategory('Privacy',3) ?:
			new DocType(
				documentType : 'Privacy',
				documentCategory:3
			).save(failOnError: true)
			
				
			
			
		
		//FAMILY TYPE
		def fType1 = FamilyType.findByValue('Coniuge') ?: new FamilyType(
			value: 'Coniuge',
		enabled: true).save(failOnError: true)
		def fType2 = FamilyType.findByValue('Figlio') ?: new FamilyType(
			value: 'Figlio',
		enabled: true).save(failOnError: true)
		def fType3 = FamilyType.findByValue('Genitore') ?: new FamilyType(
			value: 'Genitore',
		enabled: true).save(failOnError: true)
		def fType4 = FamilyType.findByValue('Fratello/Sorella') ?: new FamilyType(
			value: 'Fratello/Sorella',
		enabled: true).save(failOnError: true)
		def fType5 = FamilyType.findByValue('Altro') ?: new FamilyType(
			value: 'Altro',
		enabled: true).save(failOnError: true)

		
		//DEBIT TYPE
		def debit1 = DebitType.findByType('Mutuo') ?: new DebitType(
			type: 'Mutuo',
		enabled: true).save(failOnError: true)
		def debit2 = DebitType.findByType('Prestito Personale') ?: new DebitType(
			type: 'Prestito Personale',
		enabled: true).save(failOnError: true)
		def debit3 = DebitType.findByType('Cessione del Quinto / Delega') ?: new DebitType(
			type: 'Cessione del quinto / Delega',
		enabled: true).save(failOnError: true)
		def debit4 = DebitType.findByType('Carta di Credito') ?: new DebitType(
			type: 'Carta di Credito',
		enabled: true).save(failOnError: true)
		def debit5 = DebitType.findByType('Finanziamento') ?: new DebitType(
			type: 'Finanziamento',
		enabled: true).save(failOnError: true)
		def debit6 = DebitType.findByType('Affidamento bancario') ?: new DebitType(
			type: 'Affidamento bancario',
		enabled: true).save(failOnError: true)
		def debit7 = DebitType.findByType('Leasing') ?: new DebitType(
			type: 'Leasing',
		enabled: true).save(failOnError: true)
		def debit8 = DebitType.findByType('Imposta e tasse') ?: new DebitType(
			type: 'Imposta e tasse',
		enabled: true).save(failOnError: true)
		def debit9 = DebitType.findByType('Inps / Istit. Prev.') ?: new DebitType(
			type: 'Inps / Istit. Prev.',
		enabled: true).save(failOnError: true)
		def debit10 = DebitType.findByType('Fornitori') ?: new DebitType(
			type: 'Fornitori',
		enabled: true).save(failOnError: true)
		def debit11 = DebitType.findByType('Assegno alimenti') ?: new DebitType(
			type: 'Assegno alimenti',
		enabled: true).save(failOnError: true)
		def debit12 = DebitType.findByType('Affitto') ?: new DebitType(
			type: 'Affitto',
		enabled: true).save(failOnError: true)
		def debit13 = DebitType.findByType('Spese mediche') ?: new DebitType(
			type: 'Spese mediche',
		enabled: true).save(failOnError: true)
		def debit14 = DebitType.findByType('Spese scolastiche') ?: new DebitType(
			type: 'Spese scolastiche',
		enabled: true).save(failOnError: true)
		def debit15 = DebitType.findByType('Assicurazione') ?: new DebitType(
			type: 'Assicurazione',
		enabled: true).save(failOnError: true)
		def debit16 = DebitType.findByType('Fideiussione') ?: new DebitType(
			type: 'Fideiussione',
		enabled: true).save(failOnError: true)
		def debit17 = DebitType.findByType('Altro') ?: new DebitType(
			type: 'Altro',
		enabled: true).save(failOnError: true)
			
		
		//JOB TYPE
		def gType = JobType.findByValue('Lavoratore Dipendente') ?: new JobType(
			value: 'Lavoratore Dipendente',
		enabled: true).save(failOnError: true)
		def gType2 = JobType.findByValue('Pensionato') ?: new JobType(
			value: 'Pensionato',
		enabled: true).save(failOnError: true)
		def gType3 = JobType.findByValue('Mobilita - Cassa Integrato') ?: new JobType(
			value: 'Mobilita - Cassa Integrato',
		enabled: true).save(failOnError: true)
		def gType4 = JobType.findByValue('Disoccupato') ?: new JobType(
			value: 'Disoccupato',
		enabled: true).save(failOnError: true)
		def gType5 = JobType.findByValue('Piccolo Imprenditore') ?: new JobType(
			value: 'Piccolo Imprenditore',
		enabled: true).save(failOnError: true)
		def gType6 = JobType.findByValue('Artigiano') ?: new JobType(
			value: 'Artigiano',
		enabled: true).save(failOnError: true)
		def gType7 = JobType.findByValue('Professionista - Lavoro Autonomo') ?: new JobType(
			value: 'Professionista - Lavoro Autonomo',
		enabled: true).save(failOnError: true)
		def gType8 = JobType.findByValue('Azienda Agricola') ?: new JobType(
			value: 'Azienda Agricola',
		enabled: true).save(failOnError: true)
		def gType9 = JobType.findByValue('Disoccupato') ?: new JobType(
			value: 'Disoccupato',
		enabled: true).save(failOnError: true)
			
		
		//ANOMALIA BANCARIA TYPE
		def pType1 = AnomaliaBancariaType.findByAnomaliaBancariaType('Conto Corrente') ?: new AnomaliaBancariaType(
			anomaliaBancariaType: 'Conto Corrente', siglaAnomaliaBancaria: 'CC',
		enabled: true).save(failOnError: true)
		def pType2 = AnomaliaBancariaType.findByAnomaliaBancariaType('Mutuo') ?: new AnomaliaBancariaType(
			anomaliaBancariaType: 'Mutuo', siglaAnomaliaBancaria: 'MU',
		enabled: true).save(failOnError: true)
		def pType3 = AnomaliaBancariaType.findByAnomaliaBancariaType('Leasing') ?: new AnomaliaBancariaType(
			anomaliaBancariaType: 'Leasing', siglaAnomaliaBancaria: 'LE',
		enabled: true).save(failOnError: true)
		def pType4 = AnomaliaBancariaType.findByAnomaliaBancariaType('Derivato') ?: new AnomaliaBancariaType(
			anomaliaBancariaType: 'Derivato', siglaAnomaliaBancaria: 'DE',
		enabled: true).save(failOnError: true)


		//IMMOBILE TYPE
		def iType1 = ImmobileType.findByImmobileType('APPARTAMENTO') ?: new ImmobileType(
			immobileType: 'APPARTAMENTO',
		enabled: true).save(failOnError: true)
		def iType2 = ImmobileType.findByImmobileType('VILLA A SCHIERA') ?: new ImmobileType(
			immobileType: 'VILLA A SCHIERA',
		enabled: true).save(failOnError: true)
		def iType3 = ImmobileType.findByImmobileType('VILLA INDIPENDENTE') ?: new ImmobileType(
			immobileType: 'VILLA INDIPENDENTE',
		enabled: true).save(failOnError: true)
		def iType4 = ImmobileType.findByImmobileType('UFFICIO') ?: new ImmobileType(
			immobileType: 'UFFICIO',
		enabled: true).save(failOnError: true)
		def iType5 = ImmobileType.findByImmobileType('NEGOZIO') ?: new ImmobileType(
			immobileType: 'NEGOZIO',
		enabled: true).save(failOnError: true)
		def iType6 = ImmobileType.findByImmobileType('BOX') ?: new ImmobileType(
			immobileType: 'BOX',
		enabled: true).save(failOnError: true)
		def iType7 = ImmobileType.findByImmobileType('CAPANNONE INDUSTRIALE') ?: new ImmobileType(
			immobileType: 'CAPANNONE INDUSTRIALE',
		enabled: true).save(failOnError: true)
		def iType8 = ImmobileType.findByImmobileType('ALTRO') ?: new ImmobileType(
			immobileType: 'ALTRO',
		enabled: true).save(failOnError: true)

		
		//MOTIVE INTERVIEW TYPE
		def miType1 = MotiveInterviewType.findByMotiveInterviewType('Non perdere la casa') ?: new MotiveInterviewType(
			motiveInterviewType: 'Non perdere la casa',
		enabled: true).save(failOnError: true)
		def miType2 = MotiveInterviewType.findByMotiveInterviewType('Cedere tutto il patrimonio') ?: new MotiveInterviewType(
			motiveInterviewType: 'Cedere tutto il patrimonio',
		enabled: true).save(failOnError: true)
		def miType3 = MotiveInterviewType.findByMotiveInterviewType('Ripianare tutte le posizioni') ?: new MotiveInterviewType(
			motiveInterviewType: 'Ripianare tutte le posizioni',
		enabled: true).save(failOnError: true)
		def miType4 = MotiveInterviewType.findByMotiveInterviewType('Altro') ?: new MotiveInterviewType(
			motiveInterviewType: 'Altro',
		enabled: true).save(failOnError: true)

		
		//COMUNI SQL
		def regione=Regione.get(1);
		Sql sql = new Sql(dataSource);
		if(!regione){
			String sqlString="INSERT INTO regione (id, nome,version) VALUES (1, 'Piemonte',0),(2, 'Valle Aosta',0),(3, 'Lombardia',0),(4, 'Trentino-Alto Adige',0),(5, 'Veneto',0),(6, 'Friuli-Venezia Giulia',0),(7, 'Liguria',0),(8, 'Emilia-Romagna',0),(9, 'Toscana',0),(10, 'Umbria',0),(11, 'Marche',0),(12, 'Lazio',0),(13, 'Abruzzo',0),(14, 'Molise',0),(15, 'Campania',0),(16, 'Puglia',0),(17, 'Basilicata',0),(18, 'Calabria',0),(19, 'Sicilia',0),(20, 'Sardegna',0),(21, 'Altro/Estero',0);"
			sql.execute(sqlString);
		}
		def provincia=Provincia.get(1);
		if(!provincia){
		String	sqlString="INSERT INTO provincia (id, nome, regione_id,version) VALUES (200, 'Altro', 21,0),(1, 'Torino', 1,0),(2, 'Vercelli', 1,0),(3, 'Novara', 1,0),(4, 'Cuneo', 1,0),(5, 'Asti', 1,0),(6, 'Alessandria', 1,0),(7, 'Valle d Aosta ', 2,0),(8, 'Imperia', 7,0),(9, 'Savona', 7,0),(10, 'Genova', 7,0),(11, 'La Spezia', 7,0),(12, 'Varese', 3,0),(13, 'Como', 3,0),(14, 'Sondrio', 3,0),(15, 'Milano', 3,0),(16, 'Bergamo', 3,0),(17, 'Brescia', 3,0),(18, 'Pavia', 3,0),(19, 'Cremona', 3,0),(20, 'Mantova', 3,0),(21, 'Bolzano', 4,0),(22, 'Trento', 4,0),(23, 'Verona', 5,0),(24, 'Vicenza', 5,0),(25, 'Belluno', 5,0),(26, 'Treviso', 5,0),(27, 'Venezia', 5,0),(28, 'Padova', 5,0),(29, 'Rovigo', 5,0),(30, 'Udine', 6,0),(31, 'Gorizia', 6,0),(32, 'Trieste', 6,0),(33, 'Piacenza', 8,0),(34, 'Parma', 8,0),(35, 'Reggio Emilia', 8,0),(36, 'Modena', 8,0),(37, 'Bologna', 8,0),(38, 'Ferrara', 8,0),(39, 'Ravenna', 8,0),(40, 'Forli-Cesena', 8,0),(41, 'Pesaro e Urbino', 11,0),(42, 'Ancona', 11,0),(43, 'Macerata', 11,0),(44, 'Ascoli Piceno', 11,0),(45, 'Massa-Carrara', 9,0),(46, 'Lucca', 9,0),(47, 'Pistoia', 9,0),(48, 'Firenze', 9,0),(49, 'Livorno', 9,0),(50, 'Pisa', 9,0),(51, 'Arezzo', 9,0),(52, 'Siena', 9,0),(53, 'Grosseto', 9,0),(54, 'Perugia', 10,0),(55, 'Terni', 10,0),(56, 'Viterbo', 12,0),(57, 'Rieti', 12,0),(58, 'Roma', 12,0),(59, 'Latina', 12,0),(60, 'Frosinone', 12,0),(61, 'Caserta', 15,0),(62, 'Benevento', 15,0),(63, 'Napoli', 15,0),(64, 'Avellino', 15,0),(65, 'Salerno', 15,0),(66, 'L''Aquila', 13,0),(67, 'Teramo', 13,0),(68, 'Pescara', 13,0),(69, 'Chieti', 13,0),(70, 'Campobasso', 14,0),(71, 'Foggia', 16,0),(72, 'Bari', 16,0),(73, 'Taranto', 16,0),(74, 'Brindisi', 16,0),(75, 'Lecce', 16,0),(76, 'Potenza', 17,0),(77, 'Matera', 17,0),(78, 'Cosenza', 18,0),(79, 'Catanzaro', 18,0),(80, 'Reggio di Calabria', 18,0),(81, 'Trapani', 19,0),(82, 'Palermo', 19,0),(83, 'Messina', 19,0),(84, 'Agrigento', 19,0),(85, 'Caltanissetta', 19,0),(86, 'Enna', 19,0),(87, 'Catania', 19,0),(88, 'Ragusa', 19,0),(89, 'Siracusa', 19,0),(90, 'Sassari', 20,0),(91, 'Nuoro', 20,0),(92, 'Cagliari', 20,0),(93, 'Pordenone', 6,0),(94, 'Isernia', 14,0),(95, 'Oristano', 20,0),(96, 'Biella', 1,0),(97, 'Lecco', 3,0),(98, 'Lodi', 3,0),(99, 'Rimini', 8,0),(100, 'Prato', 9,0),(101, 'Crotone', 18,0),(102, 'Vibo Valentia', 18,0),(103, 'Verbano-Cusio-Ossola', 1,0),(104, 'Olbia-Tempio', 20,0),(105, 'Ogliastra', 20,0),(106, 'Medio Campidano', 20,0),(107, 'Carbonia-Iglesias', 20,0),(108, 'Monza e della Brianza', 3,0),(109, 'Fermo', 11,0),(110, 'Barletta-Andria-Trani', 16,0),(111, 'Altro-Estero', 21,0);"
		sql.execute(sqlString);
		}
		def comune=Comune.get(1002);
		if(!comune){
			//comuni
			ClassPathResource cpr = new ClassPathResource('comuni.sql');
			InputStream fis = cpr.getInputStream();
			int content;
			String sqlString='';
			while ((content = fis.read()) != -1) {
				// convert to char and display it
				sqlString+=((char) content);
			}
			sql.execute(sqlString);
			
			List<Comune> comFix = Comune.findAllByNomeIlike('%/%');
			if(comFix){
				comFix.each {Comune fix->
					println fix.nome
					fix.nome = fix.nome.substring(0, (fix.nome.indexOf("/")-1)); 
					fix.save()					
				}
			}
			
			ClassPathResource cprCSV = new ClassPathResource('cap.csv');
			InputStream fisCSV = cprCSV.getInputStream();
			int contentCSV;
			String sqlStringCSV='';
			List lines =fisCSV.readLines();
			if(lines){
				lines.each {line->
					StringTokenizer tok = new StringTokenizer(line, ",")
					String comune_name=tok.nextToken()
					String cap=tok.nextToken()
					Comune com = Comune.findByNome(comune_name);
					if(com){
						com.cap=cap
						com.save(failOnError: true)
					}else{
						println 'WARNING COMUNE NOT FOUND FOR CAP '+cap+' ['+comune_name+']'
					}
					
				}
			}
			
		}
		
		//messaggi antecedenti 19-03-15
		List<Message> mes = new ArrayList()
		mes= Message.findAll()
		List<User> user = new ArrayList()
		user = User.findAll()
		for(Message m:mes){
			if(m.dateEnd) {
			} else {
				m.dateEnd = m.expire
				m.save(flush:true)
			}
			if(m.eventUser){
			} else {
				for(User u:user){
					EventUser ev = new EventUser()
					ev.setUserId(new Long(u.id))
					ev.setEsito("");
					ev.save(flush:true)
					m.eventUser.add(ev)
					m.save(flush:true)
				}
			}
		}
	}
	

	
	def destroy = {
	}
	
}
